SELECT T.* FROM C1_SITE T WHERE (Sysdate BETWEEN t.begin_date AND t.end_date) AND EXISTS
  (SELECT 1 FROM C1_site_Url e WHERE e.code = :siteUrlCode and (Sysdate BETWEEN e.begin_date AND e.end_date) AND e.site_id = t.id
  )